Data Analysis Report

Prepared By: Zahiruddin Zahidanishah

Electricity Consumption Report

In [1]:
from IPython.display import display_html
def display_side_by_side(*args):
    html_str=''
    for df in args:
        html_str+=df.to_html()
    display_html(html_str.replace('table','table style="display:inline"'),raw=True)
In [2]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import plotly.graph_objects as go
import plotly.express as px
from plotly.subplots import make_subplots

#data = {'Month':['Jan', 'Feb'], 'Usage':[355,314], 'Amount':[107.05,85.55]}
#df = pd.DataFrame(data, columns=['Month', 'Usage', 'Amount'])
#df.to_excel('utilities.xlsx', sheet_name='Sheet1')
df1 = pd.read_excel('elec_utils.xlsx', sheet_name='2014')
df2 = pd.read_excel('elec_utils.xlsx', sheet_name='2015')
df3 = pd.read_excel('elec_utils.xlsx', sheet_name='2016')
df4 = pd.read_excel('elec_utils.xlsx', sheet_name='2017')
df5 = pd.read_excel('elec_utils.xlsx', sheet_name='2018')
df6 = pd.read_excel('elec_utils.xlsx', sheet_name='2019')
df7 = pd.read_excel('elec_utils.xlsx', sheet_name='2020')
df8 = pd.read_excel('elec_utils.xlsx', sheet_name='2021')
df9 = pd.read_excel('elec_utils.xlsx', sheet_name='2022')

Introduction

This report reports shows the electricity consumption and amount payable for a typical double storey house in urban city located in Selangor, Malaysia. This report consists of data from year 2014 to date. This report will shows the relation between the consumption and payment for the electricity usage every month and every year.

The details of the house inventory and areas are as describe belows.

In [3]:
data = {'Items':['Type','Land Area (sq.m)','Build Up Area (sq.m)','Bedrooms','Bathrooms',
                 'Lighting Pts.','Power Pts.','Fan Pts.','AC Pts.'],
        'Description':['Double Storey Terrace House',121,180,4,3,22,14,4,3]}
df_house = pd.DataFrame(data,columns=['Items','Description'])
df_house.style.set_caption("Assets Details")
Out[3]:
Assets Details
  Items Description
0 Type Double Storey Terrace House
1 Land Area (sq.m) 121
2 Build Up Area (sq.m) 180
3 Bedrooms 4
4 Bathrooms 3
5 Lighting Pts. 22
6 Power Pts. 14
7 Fan Pts. 4
8 AC Pts. 3
In [4]:
data = {'Year':[2014,2015,2016,2017,2018,2019,2020,2021,2022],
       'Total Usage (kWh)':[df1['Usage (kWh)'].sum(),df2['Usage (kWh)'].sum(),df3['Usage (kWh)'].sum(),
                            df4['Usage (kWh)'].sum(),df5['Usage (kWh)'].sum(),df6['Usage (kWh)'].sum(),
                            df7['Usage (kWh)'].sum(),df8['Usage (kWh)'].sum(),df9['Usage (kWh)'].sum()],
        'Ave. Usage/P (kWh/p)':[df1['Usage (kWh)'].sum()/5/12,df2['Usage (kWh)'].sum()/5/12,
                                        df3['Usage (kWh)'].sum()/5/12,df4['Usage (kWh)'].sum()/5/12,
                                        df5['Usage (kWh)'].sum()/5/12,df6['Usage (kWh)'].sum()/5/12,
                                        df7['Usage (kWh)'].sum()/5/12,df8['Usage (kWh)'].sum()/5/12,
                                        df9['Usage (kWh)'].sum()/5/12],
        'Ave. Usage/A (kWh/m2)':[df1['Usage (kWh)'].sum()/180/12,df2['Usage (kWh)'].sum()/180/12,
                                       df3['Usage (kWh)'].sum()/180/12,df4['Usage (kWh)'].sum()/180/12,
                                       df5['Usage (kWh)'].sum()/180/12,df6['Usage (kWh)'].sum()/180/12,
                                       df7['Usage (kWh)'].sum()/180/12,df8['Usage (kWh)'].sum()/180/12,
                                       df9['Usage (kWh)'].sum()/180/12],
       'Total Amount (RM)':[df1['Amount (RM)'].sum(),df2['Amount (RM)'].sum(),
                            df3['Amount (RM)'].sum(),df4['Amount (RM)'].sum(),
                            df5['Amount (RM)'].sum(),df6['Amount (RM)'].sum(),
                            df7['Amount (RM)'].sum(),df8['Amount (RM)'].sum(),
                            df9['Amount (RM)'].sum()],
       'Ave. Usage (kWh)':[df1['Usage (kWh)'].mean(),df2['Usage (kWh)'].mean(),
                           df3['Usage (kWh)'].mean(),df4['Usage (kWh)'].mean(),
                           df5['Usage (kWh)'].mean(),df6['Usage (kWh)'].mean(),
                           df7['Usage (kWh)'].mean(),df8['Usage (kWh)'].mean(),
                           df9['Usage (kWh)'].mean()],
       'Ave. Amount (RM)':[df1['Amount (RM)'].mean(),df2['Amount (RM)'].mean(),
                           df3['Amount (RM)'].mean(),df4['Amount (RM)'].mean(),
                           df5['Amount (RM)'].mean(),df6['Amount (RM)'].mean(),
                           df7['Amount (RM)'].mean(),df8['Amount (RM)'].mean(),
                           df9['Amount (RM)'].mean()],
       'Max. Usage (kWh)':[df1['Usage (kWh)'].max(),df2['Usage (kWh)'].max(),df3['Usage (kWh)'].max(),
                           df4['Usage (kWh)'].max(),df5['Usage (kWh)'].max(),df6['Usage (kWh)'].max(),
                           df7['Usage (kWh)'].max(),df8['Usage (kWh)'].max(),df9['Usage (kWh)'].max()],
       'Max. Amount (RM)':[df1['Amount (RM)'].max(),df2['Amount (RM)'].max(),
                           df3['Amount (RM)'].max(),df4['Amount (RM)'].max(),
                           df5['Amount (RM)'].max(),df6['Amount (RM)'].max(),
                           df7['Amount (RM)'].max(),df8['Amount (RM)'].max(),
                           df9['Amount (RM)'].max()]}
df = pd.DataFrame(data,columns=['Year','Total Usage (kWh)','Ave. Usage/P (kWh/p)','Ave. Usage/A (kWh/m2)',
                                'Ave. Usage (kWh)','Total Amount (RM)','Ave. Amount (RM)',
                                'Max. Usage (kWh)','Max. Amount (RM)']).astype(int)
In [5]:
df['Electricity Rate (RM/kWh)'] = df['Total Amount (RM)']/df['Total Usage (kWh)']
df = df.reindex(['Year',
                 'Total Usage (kWh)',
                 'Total Amount (RM)',
                 'Electricity Rate (RM/kWh)',
                 'Ave. Usage (kWh)',
                 'Ave. Amount (RM)',
                 'Ave. Usage/P (kWh/p)',
                 'Ave. Usage/A (kWh/m2)',
                 'Max. Usage (kWh)',
                 'Max. Amount (RM)'], axis=1)
In [6]:
df.style.set_caption("Summary of Annual Electriciy Consumption").set_table_styles([{
    'selector': 'caption',
    'props': [
        ('color', 'black'),
        ('font-size', '26px'),
        ("text-align", "center"),
        ('text-decoration', 'underline'),
        ('font-family','Arial'),
        ('text-shadow', '2px 2px 5px grey')
    ]},(dict
        (selector='th',props=[('text-align',
                               'left')]))]).format(
    {'Electricity Rate (RM/kWh)':'{:,.2f}'}
).set_properties(subset=['Year'],**{'text-align': 'left'}).hide_index()
Out[6]:
Summary of Annual Electriciy Consumption
Year Total Usage (kWh) Total Amount (RM) Electricity Rate (RM/kWh) Ave. Usage (kWh) Ave. Amount (RM) Ave. Usage/P (kWh/p) Ave. Usage/A (kWh/m2) Max. Usage (kWh) Max. Amount (RM)
2014 3645 964 0.26 303 80 60 1 350 102
2015 3569 899 0.25 297 74 59 1 405 103
2016 4498 1365 0.30 374 113 74 2 530 188
2017 4719 1491 0.32 393 124 78 2 448 153
2018 4640 1439 0.31 386 119 77 2 490 175
2019 4249 1278 0.30 354 106 70 1 444 151
2020 5080 1240 0.24 423 103 84 2 1221 164
2021 4596 1326 0.29 383 110 76 2 444 136
2022 1526 453 0.30 381 113 25 0 460 152
In [7]:
fig = make_subplots(
    rows=1, cols=2,
    specs=[[{"type": "xy"},{"type": "domain"}]],
    subplot_titles=('<b>Total Usage (kWh)</b>','<b>Total Amount (RM)</b>')
)

fig.add_trace(go.Bar(x = df['Year'], y = df['Total Usage (kWh)'],
                     name='Total Usage (kWh)'),row=1, col=1)

fig.add_trace(go.Pie(values=df['Total Amount (RM)'],
                     labels=df['Year'],
                     textinfo='label+percent'),row=1, col=2)

fig.update_layout(height=500, showlegend=False,
                 title_text='Electricity Usage & Consumption', title_x=0.5)
fig.update_annotations(font=dict(family="Helvetica", size=12))
fig.update_layout(font=dict(family="Helvetica", size=12))

fig.show()
In [8]:
bar_plots = [
    go.Bar(x = df['Year'], y = df['Ave. Usage/P (kWh/p)'],name='Total Consumption (kWh)')
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly Average Person Electricity Consumption In Respective Year',x=0.5),
yaxis_title='Usage (kWh/person)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
#fig.show()
In [9]:
bar_plots = [
    go.Bar(x = df['Year'], y = df['Ave. Usage/A (kWh/m2)'],name='Total Consumption (kWh)')
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly Average Electricity Consumption/Area In Respective Year',x=0.5),
yaxis_title='Usage (kWh/m2)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()
In [10]:
bar_plots = [
    go.Bar(x = df['Year'], y = df['Total Usage (kWh)'],name='Total Consumption (kWh)'),
    go.Bar(x = df['Year'], y = df['Total Amount (RM)'],name='Total Amount (RM)')
           ]
layout = go.Layout(
title=go.layout.Title(text='Annual Electricity Consumption (kWh) & Amount (RM)',x=0.5),
yaxis_title='Usage (kWh)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()

Detail of Electriciy Consumption and Amount for Respective Year

In [11]:
bar_plots = [
    go.Bar(x = df1[2014], y = df1['Usage (kWh)'],name=2014),
    go.Bar(x = df2[2015], y = df2['Usage (kWh)'],name=2015),
    go.Bar(x = df3[2016], y = df3['Usage (kWh)'],name=2016),
    go.Bar(x = df4[2017], y = df4['Usage (kWh)'],name=2017),
    go.Bar(x = df5[2018], y = df5['Usage (kWh)'],name=2018),
    go.Bar(x = df6[2019], y = df6['Usage (kWh)'],name=2019),
    go.Bar(x = df7[2020], y = df7['Usage (kWh)'],name=2020),
    go.Bar(x = df8[2021], y = df8['Usage (kWh)'],name=2021),
    go.Bar(x = df9[2022], y = df9['Usage (kWh)'],name=2022)
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly Electricity Consumption (kWh)',x=0.5),
yaxis_title='Usage (kWh)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()
In [12]:
bar_plots = [
    go.Bar(x = df1[2014], y = df1['Amount (RM)'],name=2014),
    go.Bar(x = df2[2015], y = df2['Amount (RM)'],name=2015),
    go.Bar(x = df3[2016], y = df3['Amount (RM)'],name=2016),
    go.Bar(x = df4[2017], y = df4['Amount (RM)'],name=2017),
    go.Bar(x = df5[2018], y = df5['Amount (RM)'],name=2018),
    go.Bar(x = df6[2019], y = df6['Amount (RM)'],name=2019),
    go.Bar(x = df7[2020], y = df7['Amount (RM)'],name=2020),
    go.Bar(x = df8[2021], y = df8['Amount (RM)'],name=2021),
    go.Bar(x = df9[2022], y = df9['Amount (RM)'],name=2022)
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly Electricity Amount (RM)',x=0.5),
yaxis_title='Amount (RM)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()

Detail of Consumption (kWh) and Amount (RM) for Every Years From 2014 to 2020

In [13]:
df1['kWh/person'] = df1['Usage (kWh)']/5
df2['kWh/person'] = df2['Usage (kWh)']/5
df3['kWh/person'] = df3['Usage (kWh)']/5
df4['kWh/person'] = df4['Usage (kWh)']/5
df5['kWh/person'] = df5['Usage (kWh)']/5
df6['kWh/person'] = df6['Usage (kWh)']/5
df7['kWh/person'] = df7['Usage (kWh)']/5
df8['kWh/person'] = df8['Usage (kWh)']/5
df9['kWh/person'] = df9['Usage (kWh)']/5
display_side_by_side(df1, df2, df3)
2014 Usage (kWh) Amount (RM) kWh/person
0 Jan 261 63.98 52.2
1 Feb 288 70.56 57.6
2 Mac 348 101.77 69.6
3 Apr 263 64.64 52.6
4 May 350 102.80 70.0
5 Jun 324 89.38 64.8
6 Jul 346 100.74 69.2
7 Aug 268 66.31 53.6
8 Sep 292 74.33 58.4
9 Oct 267 65.98 53.4
10 Nov 301 77.52 60.2
11 Dec 337 86.34 67.4
2015 Usage (kWh) Amount (RM) kWh/person
0 Jan 254 61.64 50.8
1 Feb 347 86.90 69.4
2 Mac 276 68.98 55.2
3 Apr 309 81.64 61.8
4 May 301 77.52 60.2
5 Jun 273 67.98 54.6
6 Jul 405 103.84 81.0
7 Aug 238 56.29 47.6
8 Sep 295 75.33 59.0
9 Oct 266 65.64 53.2
10 Nov 321 81.69 64.2
11 Dec 284 71.66 56.8
2016 Usage (kWh) Amount (RM) kWh/person
0 Jan 246 58.96 49.2
1 Feb 399 120.01 79.8
2 Mac 530 188.20 106.0
3 Apr 465 162.14 93.0
4 May 494 177.10 98.8
5 Jun 342 98.67 68.4
6 Jul 305 79.58 61.0
7 Aug 409 125.46 81.8
8 Sep 336 87.80 67.2
9 Oct 378 117.25 75.6
10 Nov 286 72.32 57.2
11 Dec 308 78.16 61.6
In [14]:
display_side_by_side(df4, df5, df6)
2017 Usage (kWh) Amount (RM) kWh/person
0 Jan 427 142.53 85.4
1 Feb 390 123.44 78.0
2 Mac 448 153.37 89.6
3 Apr 441 149.76 88.2
4 May 361 108.48 72.2
5 Jun 393 124.99 78.6
6 Jul 339 91.97 67.8
7 Aug 383 119.83 76.6
8 Sep 399 122.93 79.8
9 Oct 406 131.70 81.2
10 Nov 405 131.18 81.0
11 Dec 327 90.93 65.4
2018 Usage (kWh) Amount (RM) kWh/person
0 Jan 342 98.67 68.4
1 Feb 455 149.20 91.0
2 Mac 426 142.02 85.2
3 Apr 419 138.40 83.8
4 May 490 175.04 98.0
5 Jun 315 78.88 63.0
6 Jul 357 106.41 71.4
7 Aug 412 134.79 82.4
8 Sep 389 115.14 77.8
9 Oct 358 106.93 71.6
10 Nov 368 112.09 73.6
11 Dec 309 81.64 61.8
2019 Usage (kWh) Amount (RM) kWh/person
0 Jan 370 113.12 74.0
1 Feb 444 151.30 88.8
2 Mac 399 130.15 79.8
3 Apr 373 116.50 74.6
4 May 413 137.45 82.6
5 Jun 285 72.00 57.0
6 Jul 366 112.85 73.2
7 Aug 280 70.30 56.0
8 Sep 348 103.40 69.6
9 Oct 336 97.10 67.2
10 Nov 323 90.30 64.6
11 Dec 312 84.50 62.4
In [15]:
display_side_by_side(df7, df8, df9)
2020 Usage (kWh) Amount (RM) kWh/person
0 Jan 355 107.05 71.0
1 Feb 314 85.55 62.8
2 Mac 350 104.45 70.0
3 Apr 350 96.35 70.0
4 May 350 88.80 70.0
5 Jun 1221 164.40 244.2
6 Jul 368 112.10 73.6
7 Aug 344 99.70 68.8
8 Sep 360 107.96 72.0
9 Oct 336 82.55 67.2
10 Nov 391 107.05 78.2
11 Dec 341 84.80 68.2
2021 Usage (kWh) Amount (RM) kWh/person
0 Jan 322 79.55 64.4
1 Feb 378 111.55 75.6
2 Mac 378 111.55 75.6
3 Apr 361 103.00 72.2
4 May 357 100.95 71.4
5 Jun 393 119.15 78.6
6 Jul 361 103.00 72.2
7 Aug 436 122.28 87.2
8 Sep 396 108.60 79.2
9 Oct 444 136.60 88.8
10 Nov 404 124.65 80.8
11 Dec 366 105.50 73.2
2022 Usage (kWh) Amount (RM) kWh/person
0 Jan 359.0 102.00 71.8
1 Feb 323.0 83.85 64.6
2 Mac 384.0 114.60 76.8
3 Apr 460.0 152.91 92.0
4 May NaN NaN NaN
5 Jun NaN NaN NaN
6 Jul NaN NaN NaN
7 Aug NaN NaN NaN
8 Sep NaN NaN NaN
9 Oct NaN NaN NaN
10 Nov NaN NaN NaN
11 Dec NaN NaN NaN
In [16]:
bar_plots = [
    go.Bar(x = df1[2014], y = df1['kWh/person'],name=2014),
    go.Bar(x = df2[2015], y = df2['kWh/person'],name=2015),
    go.Bar(x = df3[2016], y = df3['kWh/person'],name=2016),
    go.Bar(x = df4[2017], y = df4['kWh/person'],name=2017),
    go.Bar(x = df5[2018], y = df5['kWh/person'],name=2018),
    go.Bar(x = df6[2019], y = df6['kWh/person'],name=2019),
    go.Bar(x = df7[2020], y = df7['kWh/person'],name=2020),
    go.Bar(x = df8[2021], y = df8['kWh/person'],name=2021),
    go.Bar(x = df9[2022], y = df9['kWh/person'],name=2022)
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly kwh/person Usage',x=0.5),
yaxis_title='kWh/person',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()

Details of Monthly Electricity Usage

In [17]:
px.bar(df1, x = 2014, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2014, color_continuous_scale=px.colors.sequential.Viridis)
In [18]:
px.bar(df2, x = 2015, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2015, color_continuous_scale=px.colors.sequential.Viridis)
In [19]:
px.bar(df3, x = 2016, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2016, color_continuous_scale=px.colors.sequential.Viridis)
In [20]:
px.bar(df4, x = 2017, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2017, color_continuous_scale=px.colors.sequential.Viridis)
In [21]:
px.bar(df5, x = 2018, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2018, color_continuous_scale=px.colors.sequential.Viridis)
In [22]:
px.bar(df6, x = 2019, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2019, color_continuous_scale=px.colors.sequential.Viridis)
In [23]:
px.bar(df7, x = 2020, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2020, color_continuous_scale=px.colors.sequential.Viridis)
In [24]:
px.bar(df8, x = 2021, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2021, color_continuous_scale=px.colors.sequential.Viridis)
In [25]:
px.bar(df9, x = 2022, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2022, color_continuous_scale=px.colors.sequential.Viridis)

Details of Monthly Electricity Cost

In [26]:
px.bar(df1, x = 2014, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2014, color_continuous_scale=px.colors.sequential.Viridis)
In [27]:
px.bar(df2, x = 2015, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2015, color_continuous_scale=px.colors.sequential.Viridis)
In [28]:
px.bar(df3, x = 2016, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2016, color_continuous_scale=px.colors.sequential.Viridis)
In [29]:
px.bar(df4, x = 2017, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2017, color_continuous_scale=px.colors.sequential.Viridis)
In [30]:
px.bar(df5, x = 2018, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2018, color_continuous_scale=px.colors.sequential.Viridis)
In [31]:
px.bar(df6, x = 2019, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2019, color_continuous_scale=px.colors.sequential.Viridis)
In [32]:
px.bar(df7, x = 2020, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2020, color_continuous_scale=px.colors.sequential.Viridis)
In [33]:
px.bar(df8, x = 2021, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2021, color_continuous_scale=px.colors.sequential.Viridis)
In [34]:
px.bar(df9, x = 2022, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2022, color_continuous_scale=px.colors.sequential.Viridis)

Note: This report is prepared by Zahiruddin Zahidanishah. This report is only for educational purposed and shall not be used for any commercial purposed.